﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace SomaiNordeste.FabricaRacao.DomainModel.Repository
{
    public class InsumoRepository : IInsumo
    {
        private string _connectionString;

        public InsumoRepository()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString;
        }

        public void Add(Insumo insumo)
        {
            string querySql =
                " INSERT INTO [FabricaRacao].[fabrica].[Consumo_Fab] " +
                "   ([E3TimeStamp] " +
                "   ,[Produto] " +
                "   ,[Produto_Quality] " +
                "   ,[Peso] " +
                "   ,[Peso_Quality] " +
                "   ,[Batida_Diaria] " +
                "   ,[Batida_Diaria_Quality] " +
                "   ,[Peso_Atualizado]) " +
                "   VALUES " +
                "   ('" + DateFormateInsert(insumo) + "' " +
                "   ,'" + insumo.Produto.Replace("_", " ") + "' " +
                "   ,'" + insumo.Produto_Quality + "' " +
                "   ,'" + insumo.Peso.ToString().Replace(",",".") + "' " +
                "   ,'" + insumo.Peso_Quality + "' " +
                "   ,'" + insumo.Batida_Diaria + "' " +
                "   ,'" + insumo.Batida_Diaria_Quality + "' " +
                "   ,'" + insumo.Peso_Atualizado.ToString().Replace(",", ".") + "') ";

            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = querySql;

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public string DateFormateInsert(Insumo _insumo)
        {
            //2010-11-20 13:38:43.140
            string ano = _insumo.Date.Year.ToString();
            string mes = _insumo.Date.Month > 9 ? _insumo.Date.Month.ToString() : "0" + _insumo.Date.Month.ToString();
            string dia = _insumo.Date.Day > 9 ? _insumo.Date.Day.ToString() : "0" + _insumo.Date.Day.ToString();
            string hora = DateTime.Now.Hour > 9 ? DateTime.Now.Hour.ToString() : "0" + DateTime.Now.Hour.ToString();
            string minutos = DateTime.Now.Minute > 9 ? DateTime.Now.Minute.ToString() : "0" + DateTime.Now.Minute.ToString();
            string segundos = DateTime.Now.Second > 9 ? DateTime.Now.Second.ToString() : "0" + DateTime.Now.Second.ToString();

            string millisecond = DateTime.Now.Millisecond > 100 ? DateTime.Now.Millisecond.ToString() : "00" + DateTime.Now.Millisecond.ToString();
         

            return ano + "-" +
                mes + "-" +
                dia +
                " " +
                hora + ":" +
                minutos + ":" +
                segundos + ":" +
                millisecond;
        }

        public void Save(Insumo insumo)
        {
            throw new NotImplementedException();
        }

        public void Update(Insumo insumo)
        {
            string queryString =
                " UPDATE [FabricaRacao].[fabrica].[Consumo_Fab] " + 
                "      SET [Peso_Atualizado] = '" + insumo.Peso_Atualizado.ToString().Replace(",", ".") + "' " +
                " WHERE  E3TimeStamp = '" + DateFormate(insumo) + "' AND [Produto] = '"+insumo.Produto+"' ";

            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public string DateFormate(Insumo _insumo)
        {
            //2010-11-20 13:38:43.140
            string ano = _insumo.Date.Year.ToString();
            string mes = _insumo.Date.Month > 9 ? _insumo.Date.Month.ToString() : "0" + _insumo.Date.Month.ToString();
            string dia = _insumo.Date.Day > 9 ? _insumo.Date.Day.ToString() : "0" + _insumo.Date.Day.ToString();
            string hora = _insumo.Date.Hour > 9 ? _insumo.Date.Hour.ToString() : "0" + _insumo.Date.Hour.ToString();
            string minutos = _insumo.Date.Minute > 9 ? _insumo.Date.Minute.ToString() : "0" + _insumo.Date.Minute.ToString();
            string segundos = _insumo.Date.Second > 9 ? _insumo.Date.Second.ToString() : "0" + _insumo.Date.Second.ToString();
            string millisecond = _insumo.Date.Millisecond > 100 ? _insumo.Date.Millisecond.ToString() : "00" + _insumo.Date.Millisecond.ToString();

            return ano + "-" +
                mes + "-" +
                dia +
                " " +
                hora + ":" +
                minutos + ":" +
                segundos + ":" +
                millisecond;
        }

        public IEnumerable<Insumo> FindAll()
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Insumo> FindAll(Insumo insumo)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Insumo> FindBy(DateTime date)
        {
            string queryString =
              " SELECT " +
              "    BAT.DESCRICAO " +
              " FROM FABRICA.CONSUMO_FAB CON (nolock) " +
              " INNER JOIN fabrica.Batidas BAT (nolock) ON BAT.BATIDA_DIARIA = CON.BATIDA_DIARIA AND  " +
              " DATEADD([DAY], 0, DATEDIFF([DAY], 0, CON.E3TIMESTAMP))  = DATEADD([DAY], 0, DATEDIFF([DAY], 0, BAT.E3TIMESTAMP))  " +
              " WHERE CON.PESO >= 0  AND CONVERT(VARCHAR, CON.E3TIMESTAMP, 103) = '" + date.ToShortDateString() + "' " +
              " GROUP BY BAT.DESCRICAO,DATEADD([DAY], 0, DATEDIFF([DAY], 0, CON.E3TIMESTAMP))  " +
              " ORDER BY BAT.DESCRICAO ASC ";

            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                connection.Open();

                IList<Insumo> insumos = new List<Insumo>();
                insumos.Add(new Insumo() { Racao = "Escolha a Ração" });
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Insumo batida = new Insumo();
                        batida.Racao = reader.GetString(0);
                        insumos.Add(batida);
                    }

                    return insumos;
                }
            }
        }

        public DataTable FindBy(Insumo insumo, DateTime date)
        {
            string queryString =
           " SELECT " +
           "      CON.E3TIMESTAMP AS [DATAHORA], " +
           "      BAT.DESCRICAO, " +
           "      BAT.BATIDA, " +
           "      CON.PRODUTO AS [INSUMO],  "+
           "      SUM( CON.PESO) AS [PESO], " +
           "      CON.PESO_ATUALIZADO       " +
           "  FROM FABRICA.CONSUMO_FAB CON (nolock) " +
           "  INNER JOIN fabrica.Batidas BAT (nolock) ON BAT.BATIDA_DIARIA = CON.BATIDA_DIARIA AND  " +
           "  CONVERT(VARCHAR, CON.E3TIMESTAMP, 103) = CONVERT(VARCHAR, BAT.E3TIMESTAMP, 103) " +
           "  WHERE CON.PESO >= 0  AND CONVERT(VARCHAR, CON.E3TIMESTAMP,103) =  '"+date.ToShortDateString()+"' "+
           "  AND BAT.Descricao = '" + insumo.Racao + "'" +
           "  GROUP BY CONVERT(VARCHAR, CON.E3TIMESTAMP, 103),CON.E3TIMESTAMP,BAT.DESCRICAO, CON.PRODUTO, CON.Peso,BAT.Batida, CON.PESO_ATUALIZADO " +
           "  ORDER BY CON.E3TIMESTAMP DESC, BAT.DESCRICAO ASC, BAT.BATIDA ASC ";

            using (SqlConnection connection =
                   new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                connection.Open();

                IList<Insumo> insumos = new List<Insumo>();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Insumo ins = new Insumo();
                        ins.Date = reader.GetDateTime(0);
                        ins.Milesegundos = reader.IsDBNull(0) ? "" : (reader.GetDateTime(0).Millisecond.ToString().Length > 2 ? reader.GetDateTime(0).Millisecond.ToString() : "0" + reader.GetDateTime(0).Millisecond.ToString());
                        ins.Racao = reader.IsDBNull(1) ? "" : reader.GetString(1);
                        ins.Batida_Diaria = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
                        ins.Batida = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
                        ins.Produto = reader.IsDBNull(3) ? "" : reader.GetString(3);
                        ins.Peso = reader.IsDBNull(4) ? 0 : reader.GetDouble(4);
                        ins.Peso_Atualizado = reader.IsDBNull(5) ? 0 : reader.GetDouble(5);

                        insumos.Add(ins);
                    }

                    return RetornaBatidas(insumos);
                }
            }
        }

        private DataColumn[] Colunas()
        {
            DataColumn[] columns = new DataColumn[5];

            DataColumn Date = new DataColumn();
            Date.ColumnName = "Data";
            columns[0] = Date;

            DataColumn Insumo = new DataColumn();
            Insumo.ColumnName = "Insumo";
            columns[1] = Insumo;

            DataColumn Batida = new DataColumn();
            Batida.ColumnName = "Batida";
            columns[2] = Batida;

            DataColumn KG_Total = new DataColumn();
            KG_Total.ColumnName = "Peso";
            columns[3] = KG_Total;

            DataColumn KG_Total_Atualizado = new DataColumn();
            KG_Total_Atualizado.ColumnName = "Peso_Atualizado";
            columns[4] = KG_Total_Atualizado;

            return columns;
        }


        private DataTable RetornaBatidas(IList<Insumo> insumos)
        {

            DataSet dataSet = new DataSet();
            DataTable table = new DataTable();

            dataSet.Tables.Add(table);
            table.Columns.AddRange(Colunas());

            foreach (var ins in insumos)
            {
                DataRow row = table.NewRow();
                row["Data"] = ins.Date + "." + ins.Milesegundos.ToString();
                row["Insumo"] = ins.Produto;
                row["Batida"] = ins.Batida;
                row["Peso"] = ins.Peso;
                row["Peso_Atualizado"] = ins.Peso == 0 ? ins.Peso : ins.Peso_Atualizado;
                table.Rows.Add(row);
            }

            return table;
        }

    }
}
